package com.tim.util;

import lombok.Data;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.CollectionUtils;

import java.io.File;
import java.io.RandomAccessFile;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigInteger;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.*;
import java.util.Date;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * @Description: 同步mysql数据到本地h2，未完成
 * @Version 1.0, 2021/12/3
 * @Author Tim
 */
public class SyncDataUtil {
    public static Connection syncInfoConn;// 同步库
    public static Connection syncDataConn;// 同步库
//    public static String schema;// 同步库
    public static Connection h2Conn;// 本地h2
    public static String drop_table_template = "DROP TABLE IF EXISTS `%s`;";
    public static String create_table_template = "CREATE TABLE `%1$s`  (\n%2$s\n) COMMENT = '%3$s';";

    public static void main(String[] args) throws Exception {
        File file = new File(String.format("E:/%s", System.currentTimeMillis()));
        file.createNewFile();
        init();
        /* step1，按schema获取table列表*/
        String schema = "pig_biz";
        List<TablesDO> tablesDOS = getTables(schema);
        Map<String, TablesDO> tableMap = tablesDOS.stream()
                .collect(Collectors.toMap(TablesDO::getTableName, Function.identity()));

        /* step2，按schema获取column，按table分组；删除、创建表；同步100数据*/
        List<ColumnsDO> columnsDOS = getColumns(schema);
        Map<String, List<ColumnsDO>> tableColumnMap = columnsDOS.stream()
                .collect(Collectors.groupingBy(ColumnsDO::getTableName));

        List<KeyColumnUsageDO> indexsDOS = getIndexs(schema);
        Map<String, List<KeyColumnUsageDO>> tableIndexMap = indexsDOS.stream()
                .collect(Collectors.groupingBy(KeyColumnUsageDO::getTableName));

        /* step3，删除、创建表；同步100数据*/
        // 切换schema
        h2Conn.createStatement().execute("use "+schema);
        RandomAccessFile raf = new RandomAccessFile(file, "rw");
        for(String tableName : tableMap.keySet()){
            /* step3.1，构建DDL*/
            // 构建字段信息
            String tableComment = tableMap.get(tableName).getTableComment();
            List<ColumnsDO> columnsDOList = tableColumnMap.get(tableName);
            Collections.sort(columnsDOList, Comparator.comparingLong(ColumnsDO::getOrdinalPosition));
            String columns = columnsDOList.stream()
                    .map(SyncDataUtil::getSqlColumn)
                    .collect(Collectors.joining(",\n"));
//            System.out.println(columns);
            // 构建索引信息
            String indexs = "";
            List<KeyColumnUsageDO> keyColumnUsageDOS = tableIndexMap.get(tableName);
            if(!CollectionUtils.isEmpty(keyColumnUsageDOS)){
                Map<String, List<KeyColumnUsageDO>> indexMap = keyColumnUsageDOS.stream()
                        .collect(Collectors.groupingBy(KeyColumnUsageDO::getConstraintName));
                // 找出唯一索引列
                List<String> uniColumns = columnsDOS.stream()
                        .filter(column -> "UNI".equals(column.getColumnKey()))
                        .map(ColumnsDO::getColumnName)
                        .collect(Collectors.toList());
                indexs = indexMap.values().stream()
                        .map(index -> SyncDataUtil.getSqlIndex(uniColumns, index))
                        .collect(Collectors.joining(",\n"));
                indexs = ",\n"+indexs;
//                System.out.println(indexs);
            }
            String dropTableDdl = String.format(drop_table_template, tableName);
            raf.write((dropTableDdl+"\n").getBytes(StandardCharsets.UTF_8));
//            h2Conn.createStatement().execute(dropTableDdl);
            String createTableDdl = String.format(create_table_template, tableName, columns + indexs, tableComment);
            raf.write((createTableDdl+"\n\n").getBytes(StandardCharsets.UTF_8));
//            h2Conn.createStatement().execute(createTableDdl);
        }

    }

    /**
     * 初始化连接
     */
    public static void init() throws Exception {
        initPig();
//        initPigear();
        h2Conn = DbUtils.getConnection(
                "jdbc:h2:tcp://localhost/~/pigear",
                "sa",
                ""
        );
    }

    public static void initPig() throws Exception {
        syncInfoConn = DbUtils.getConnection(
                "jdbc:mysql://rm-bp158x381519cc14qjo.mysql.rds.aliyuncs.com:3306/information_schema",
                "dev_admin",
                "Dev+2019@lazypig"
        );
        syncDataConn = DbUtils.getConnection(
                "jdbc:mysql://rm-bp158x381519cc14qjo.mysql.rds.aliyuncs.com:3306/pigear_biz",
                "dev_admin",
                "Dev+2019@lazypig"
        );

    }

    public static void initPigear() throws Exception {
        syncInfoConn = DbUtils.getConnection(
                "jdbc:mysql://rm-bp158x381519cc14qjo.mysql.rds.aliyuncs.com:3306/information_schema",
                "dev_admin",
                "Dev+2019@lazypig"
        );
        syncDataConn = DbUtils.getConnection(
                "jdbc:mysql://rm-bp158x381519cc14qjo.mysql.rds.aliyuncs.com:3306/pigear_biz",
                "dev_admin",
                "Dev+2019@lazypig"
        );

    }

    private static List<TablesDO> getTables(String schema) throws SQLException, InvocationTargetException, IllegalAccessException, InstantiationException {
        String queryTableSql = String.format("select * from TABLES where TABLE_SCHEMA='%s'",
                schema);
        ResultSet resultSet = syncInfoConn.createStatement().executeQuery(queryTableSql);
        List<TablesDO> list = resultSet2Entity(resultSet, TablesDO.class);
        return list;
    }

    private static <T> List<T> resultSet2Entity(ResultSet resultSet, Class<T> clazz) throws SQLException, IllegalAccessException, InvocationTargetException, InstantiationException {
        /* step1，记录列、setter对应关系*/
        Map<String, Method> columnMethodRef = new HashMap<>(32);
        ResultSetMetaData metaData = resultSet.getMetaData();
        Map<String, Method> setterMethodMap = Arrays.stream(clazz.getMethods())
                .filter(method->method.getName().startsWith("set"))
                .collect(Collectors.toMap(Method::getName, Function.identity()));
        for(int i=1; i<metaData.getColumnCount()+1; i++){
            String columnLabel = metaData.getColumnLabel(i);
            String methodName = "set"+StringAnalysisUtil.toPascal(columnLabel.toLowerCase());
            columnMethodRef.put(columnLabel, setterMethodMap.get(methodName));
        }

        /* step2，反射创建对象，反射设置值*/
        List<T> list = new ArrayList<>(resultSet.getFetchSize());
        while(resultSet.next()){
            T tablesDO = clazz.newInstance();
            for(String column : columnMethodRef.keySet()){
//                System.out.println(column);
                Method setter = columnMethodRef.get(column);
                Object columnVal = resultSet.getObject(column);;
                setter.invoke(tablesDO, handleSpecialVal(columnVal));
            }
            list.add(tablesDO);
        }
        return list;
    }

    private static Object handleSpecialVal(Object columnVal) throws SQLException {
        if(columnVal instanceof BigInteger){
            columnVal = ((BigInteger)columnVal).longValue();
        }
//        if(columnVal instanceof Timestamp){
//            long millis = ((Timestamp) columnVal).getTime();
//            columnVal = new Date(millis);
//        }
        return columnVal;
    }

    private static List<ColumnsDO> getColumns(String schema) throws Exception {
        String queryTableSql = String.format("select * from COLUMNS where TABLE_SCHEMA='%s'",
                schema);
        ResultSet resultSet = syncInfoConn.createStatement().executeQuery(queryTableSql);
        List<ColumnsDO> list = resultSet2Entity(resultSet, ColumnsDO.class);
        return list;
    }

    private static List<KeyColumnUsageDO> getIndexs(String schema) throws Exception {
        String queryTableSql = String.format("select * from KEY_COLUMN_USAGE where TABLE_SCHEMA='%s'",
                schema);
        ResultSet resultSet = syncInfoConn.createStatement().executeQuery(queryTableSql);
        List<KeyColumnUsageDO> list = resultSet2Entity(resultSet, KeyColumnUsageDO.class);
        return list;
    }

    public static String getSqlColumn(ColumnsDO columnsDO){
        // 列名、类型、是否为空、默认值、更新时操作、注释
        String column_template = "`%1$s` %2$s %3$s %4$s %5$s COMMENT '%6$s'";

        String columnName = columnsDO.getColumnName();
        String columnType = columnsDO.getColumnType();
        columnType = toH2Type(columnType);
        String isNullable = columnsDO.getIsNullable();
        isNullable = "NO".equals(isNullable) ? "NOT NULL" : "";
        String columnDefault = columnsDO.getColumnDefault();
        columnDefault = columnDefault==null ? ""
                : StringUtils.isBlank(columnDefault)||columnType.startsWith("varchar") ? String.format("DEFAULT '%s'", columnDefault.replaceAll("[\r\n]+", " "))
                : "DEFAULT "+columnDefault.replaceAll("[\r\n]+", " ");
        String extra = columnsDO.getExtra();
        String columnComment = columnsDO.getColumnComment();
        return String.format(column_template,
                columnName,
                columnType,
                isNullable,
                columnDefault,
                extra,
                columnComment);
    }

    /**处理不兼容类型*/
    private static String toH2Type(String columnType) {
        return columnType.replace("json", "varchar(3000)")
                .replace("double", "DECIMAL");
    }

    public static String getSqlIndex(List<String> uniColumns, List<KeyColumnUsageDO> indexList){
        // 索引类型、索引名、索引字段
        String index_template = "%1$s KEY %2$s (%3$s)";

        // 构建索引
        Collections.sort(indexList, Comparator.comparingLong(KeyColumnUsageDO::getOrdinalPosition));
        String constraintName = "";
        String indexPre = "";
        StringBuilder builder = new StringBuilder();
        for (int i = 0; i < indexList.size(); i++) {
            KeyColumnUsageDO keyColumnUsageDO = indexList.get(0);
            String columnName = keyColumnUsageDO.getColumnName();
            if(i==0){
                // 获取索引名称、索引类型
                constraintName = keyColumnUsageDO.getConstraintName();
                if("PRIMARY".equals(constraintName)){
                    indexPre = "PRIMARY";
                    constraintName = "";
                }else if(uniColumns.contains(columnName)){
                    indexPre = "UNIQUE";
                    constraintName = "`"+constraintName+"`";
                }
            }
            builder.append("`")
                    .append(columnName)
                    .append("`,");
        }
        return String.format(index_template,
                indexPre,
                constraintName,
                builder.substring(0, builder.length()-1));
    }

}


/**
 * @Description:

 * @Version 1.0, 2021年12月03日
 * @Author Tim
 *
 */
@Data
class TablesDO {

    private static final long serialVersionUID = 1L;

    /**
     *
     */
    private String tableCatalog;

    /**
     *
     */
    private String tableSchema;

    /**
     *
     */
    private String tableName;

    /**
     *
     */
    private String tableType;

    /**
     *
     */
    private String engine;

    /**
     *
     */
    private Long version;

    /**
     *
     */
    private String rowFormat;

    /**
     *
     */
    private Long tableRows;

    /**
     *
     */
    private Long avgRowLength;

    /**
     *
     */
    private Long dataLength;

    /**
     *
     */
    private Long maxDataLength;

    /**
     *
     */
    private Long indexLength;

    /**
     *
     */
    private Long dataFree;

    /**
     *
     */
    private Long autoIncrement;

    /**
     *
     */
    private Date createTime;

    /**
     *
     */
    private Date updateTime;

    /**
     *
     */
    private Date checkTime;

    /**
     *
     */
    private String tableCollation;

    /**
     *
     */
    private Long checksum;

    /**
     *
     */
    private String createOptions;

    /**
     *
     */
    private String tableComment;
}



/**
 * @Description:

 * @Version 1.0, 2021年12月03日
 * @Author Tim
 *
 */
@Data
class ColumnsDO {

    private static final long serialVersionUID = 1L;

    /**
     *
     */
    private String tableCatalog;

    /**
     *
     */
    private String tableSchema;

    /**
     *
     */
    private String tableName;

    /**
     *
     */
    private String columnName;

    /**
     *
     */
    private Long ordinalPosition;

    /**
     *
     */
    private String columnDefault;

    /**
     *
     */
    private String isNullable;

    /**
     *
     */
    private String dataType;

    /**
     *
     */
    private Long characterMaximumLength;

    /**
     *
     */
    private Long characterOctetLength;

    /**
     *
     */
    private Long numericPrecision;

    /**
     *
     */
    private Long numericScale;

    /**
     *
     */
    private Long datetimePrecision;

    /**
     *
     */
    private String characterSetName;

    /**
     *
     */
    private String collationName;

    /**
     *
     */
    private String columnType;

    /**
     *
     */
    private String columnKey;

    /**
     *
     */
    private String extra;

    /**
     *
     */
    private String privileges;

    /**
     *
     */
    private String columnComment;

    /**
     *
     */
    private String generationExpression;
}


/**
 * @Description:

 * @Version 1.0, 2021年12月03日
 * @Author Tim
 *
 */
@Data
class KeyColumnUsageDO {

    private static final long serialVersionUID = 1L;

    /**
     *
     */
    private String constraintCatalog;

    /**
     *
     */
    private String constraintSchema;

    /**
     *
     */
    private String constraintName;

    /**
     *
     */
    private String tableCatalog;

    /**
     *
     */
    private String tableSchema;

    /**
     *
     */
    private String tableName;

    /**
     *
     */
    private String columnName;

    /**
     *
     */
    private Long ordinalPosition;

    /**
     *
     */
    private Long positionInUniqueConstraint;

    /**
     *
     */
    private String referencedTableSchema;

    /**
     *
     */
    private String referencedTableName;

    /**
     *
     */
    private String referencedColumnName;
}
